import os
import time
import pandas as pd
import pyautogui
import re
import json
import datetime
import pytz
import uuid
import csv
import requests
import luigi
from bson.objectid import ObjectId
import xlrd
from faker import Faker
from config.configs import *
import openpyxl
import random
from datetime import datetime, timedelta, timezone
import pymongo
from config.configs import TEST_PORT, TEST_DB_HOST

alpha = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).alpha
jiwu = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).jiwu
taowu = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).taowu
kream = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).kream
goat = pymongo.MongoClient(GOAT_ERP_HOST, GOAT_ERP_PORT).goat_warehouse_dev
korea = pymongo.MongoClient(GOAT_ERP_HOST, GOAT_ERP_PORT).goat_warehouse_korea
test = pymongo.MongoClient(TEST_DB_HOST, TEST_PORT).workflow_dev
ding = pymongo.MongoClient(host=DINGSTOCK_HOST, port=DINGSTOCK_PORT, authMechanism='DEFAULT', authSource='admin',
                           directConnection=True)
trades = ding.tradingMarketSneaker
local = pymongo.MongoClient('127.0.0.1', 27017).test



def get_current_position():
    """获取当前鼠标所在坐标"""
    current_position = pyautogui.position()
    print("当前鼠标位置:", current_position)


def read_xlsx_file(file, num):
    """读取xlsx文件指定列内容"""
    df = pd.read_excel(file)
    li = []
    for i in df.iloc[:, num]:
        li.append(i)
    return li


def read_xlsx_all(file):
    """读取xlsx文件，返回全部数据"""
    res = pd.read_excel(file, engine='openpyxl')
    return res


def extract_numbers(s):
    # 使用正则表达式查找所有数字
    numbers = re.findall(r'\d+', s)
    # 如果需要返回单个字符串，可以将数字列表连接在一起
    return ''.join(numbers)


def extract_letters(input_string):
    # 使用正则表达式匹配所有字母（包括大小写）
    letters = re.findall(r'[a-zA-Z]', input_string)
    # 将找到的字母连接成一个字符串并返回
    return ''.join(letters)


def read_xls_file(file, sheet_index, col_index):
    """读取xls文件指定列内容"""
    workbook = xlrd.open_workbook(file)
    open_sheet = workbook.sheet_by_index(sheet_index)
    col_values = open_sheet.col_values(col_index)
    return [col_values]


def get_self_pickup_file(limit, skip):
    """创建goat自提入库单xlsx文件"""
    mobile_list = [17313699441, 19071465376]
    email_list = [1596096369, 3328287875]
    a = random.randint(10000, 99001)
    uniquecode_list = ['D' + str(a + i) for i in range(limit)]
    articlenumber_list = [i['sku'] for i in goat.goatskus.find({}).limit(limit).skip(skip)]
    data = [['唯一码', '货号', '尺码', '手机号', '邮箱', '类型', '平台订单号']]
    for i in range(1, limit):
        data.append([uniquecode_list[i], articlenumber_list[i], random.randint(36, 44),
                     random.choice(mobile_list),
                     '{}@qq.com'.format(random.choice(email_list)), 'Goat自提',
                     random.randint(10000000001, 999999999999)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '库存'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_self_pickup.xlsx'
    workbook.save(file_path)


def get_deposit_file(limit, skip):
    """创建goat寄存预约单xlsx文件"""
    articlenumber_list = [i['articleNumber'] for i in jiwu.find_limit('spus', {}, limit, skip)]
    merchantcode_list = [i['code'] for i in korea.merchants.find({})]
    data = [['货号', '规格', '数量', '商家编号']]
    for i in range(1, limit):
        data.append([articlenumber_list[i], random.randint(37, 42), random.randint(1, 10),
                     random.choice(merchantcode_list)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '入库预约单'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_depoist.xlsx'
    workbook.save(file_path)


def get_self_pickup_file_new(limit, skip):
    """创建goat最新自提入库单xlsx文件"""
    mobile_list = [17313699441, 19071465376]
    email_list = [1596096369, 3328287875]
    a = random.randint(10000, 99001)
    uniquecode_list = ['D' + str(a + i) for i in range(limit)]
    articlenumber_list = [i['sku'] for i in goat.goatskus.find({}).limit(limit).skip(skip)]
    data = [['订单号', '订单状态', '下单时间', '鉴定编号', '商品链接', '商品货号', '规格型号', '中文商品描述',
             '商品尺码 (US码)',
             '商品名称', '商品描述', '快递公司', '快递面单号', '收件人姓名', '用户手机', '收件人手机', '用户邮箱',
             '地址行1', '地址行2', '市', '州', '区', '数量', '零售价格', '订单价格', '订单总价']]
    for i in range(1, limit):
        data.append([random.randint(10000000001, 999999999999), 'goat_pick', uniquecode_list[i], articlenumber_list[i],
                     random.randint(36, 44),
                     random.choice(mobile_list),
                     '{}@qq.com'.format(random.choice(email_list)), 'Goat自提',
                     random.randint(10000000001, 999999999999)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '库存'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_self_pickup.xlsx'
    workbook.save(file_path)


def write_xlsx(title_array, array, name):
    """
    数据写入xlsx文件
    :param title_array:写入文档的首行，以字符串数组传递
    :param array:写入内容，每一行一个数组
    :param name:文件名
    """
    data = [title_array] + array
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = 'sheet1'
    for row in data:
        sheet.append(row)
    file_path = os.path.join(DATA_PATH, f'{name}.xlsx')
    try:
        workbook.save(file_path)
    except Exception as e:
        print(f"Error saving file: {e}")
        raise


def timestamp_to_date(file_path, num, title, name):
    """
    时间戳转为日期格式，写入导指定文件
    param file:需要读取的文件名
    param num:读取第几列
    param title:写入文件的标题
    param datas:写入的数据
    param name:新创建的文件名
    """
    times = read_xlsx_file(file=file_path, num=num)
    formatted_list = [datetime.datetime.utcfromtimestamp(times[i] / 1000).strftime('%Y-%m-%d %H:%M:%S') for i in
                      range(len(times))]
    time_datas = [[i] for i in formatted_list]
    write_xlsx(title, time_datas, name)


def spuId_to_articleNumber(db, spuId):
    """spuid转货号"""
    if db == 'jiwu':
        result = jiwu.spus.find_one({"spuId": spuId})
    elif db == 'korea':
        result = korea.spus.find_one({"spuId": spuId})
    else:
        result = alpha.spus.find_one({"spuId": spuId})
    if result is not None:
        return result['articleNumber']
    else:
        return None


def skuId_to_size(db, skuId):
    """skuId转尺码"""
    if db == 'jiwu':
        result = jiwu.skus.find_one({"skuId": skuId})
    elif db == 'korea':
        result = korea.skus.find_one({"skuId": skuId})
    else:
        result = jiwu.skus.find_one({"skuId": skuId})
    if result is not None and '尺码' in result['properties']:
        return result['properties'].split('"尺码":"')[1].split('"')[0]
    elif result is not None and '尺码' not in result['properties']:
        return result['properties']
    elif result is None:
        return skuId
    else:
        return result


def skuId_to_articleNumber(skuId):
    """skuId转货号"""
    result = jiwu.skus.find_one({"skuId": skuId})
    if result is not None:
        sp = result['spuId']
        rs = jiwu.spus.find_one({'spuId': sp})
        return rs['articleNumber']
    else:
        return skuId


def timestamp_switch_date(timestamp):
    """时间戳转日期"""
    shanghai_tz = pytz.timezone('Asia/Shanghai')
    if len(str(timestamp)) >= 13:
        res = datetime.utcfromtimestamp(timestamp / 1000)
        shanghai_dt = res.replace(tzinfo=timezone.utc).astimezone(shanghai_tz)
        return shanghai_dt.strftime('%Y-%m-%d %H:%M:%S')
    elif len(str(timestamp)) == 10:
        res = datetime.utcfromtimestamp(timestamp)
        shanghai_dt = res.replace(tzinfo=timezone.utc).astimezone(shanghai_tz)
        return shanghai_dt.strftime('%Y-%m-%d %H:%M:%S')
    else:
        return None


def replace_column_values(file_path, sheet_name, column_index, old_value, new_value):
    """修改xlsx文件指定列内容"""
    # 加载工作簿和工作表
    workbook = openpyxl.load_workbook(file_path)
    worksheet = workbook[sheet_name]
    # 遍历指定列的所有单元格
    for row in worksheet.iter_rows(min_row=worksheet.min_row, max_col=worksheet.max_column, values_only=False):
        cell = row[column_index]  # 将列字母转换为列索引（假设列字母是大写）
        if cell.value == old_value:
            cell.value = new_value
            # 保存更改
    workbook.save(file_path)
    workbook.close()


def read_json_file(file):
    """读取json文件"""
    with open(file, 'r', encoding='utf-8') as f:
        result = json.load(f)
        return result


def write_spu_json_file(file, data):
    """写入货号尺码json文件"""
    with open(file, 'r', encoding='utf-8') as f:
        result = json.load(f)
    result['items'].append(data)
    with open(file, 'w', encoding='utf-8') as f:
        json.dump(result, f, ensure_ascii=False, indent=4)


def write_json_file(file, data):
    """写入json文件"""
    with open(file, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=4)


def reset_json(file):
    """重置json文件数据"""
    with open(file, 'r', encoding='utf-8') as f:
        result = json.load(f)
    result['items'] = []
    with open(file, 'w', encoding='utf-8') as f:
        json.dump(result, f, ensure_ascii=False, indent=4)


def remove_null(file):
    """json文件移除null"""
    res = read_json_file(file)['items']
    li = []
    for i in res:
        if i is not None:
            li.append(i)
    data = {
        "items": li
    }
    with open(file, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=4)


def read_csv_all(file):
    """读取csv文件"""
    df = pd.read_csv(file)
    all_rows = df.values.tolist()
    return all_rows


def now_timestamp():
    """当前时间戳"""
    return int(round(time.time() * 1000))


def update_colum_datas(filepath, colum_name, new_data):
    """修改xlsx文件指定列内容"""
    # 读取原 xlsx 文件
    df = pd.read_excel(filepath)
    # 假设要覆盖的列为 'column_name' ，新的数据为 new_data
    df[colum_name] = new_data
    # 保存覆盖后的文件
    df.to_excel(filepath, index=False)


def articleNumber_to_spuId(art):
    res = jiwu.spus.find_one({"articleNumber": art})
    if res is None:
        print(f"没有找到货号 {art}")
    return res['spuId']


def deal_float(num):
    """四舍五入"""
    if type(num) == int:
        return num
    elif type(num) == float:
        return round(num, 2)


def get_jiwu_token():
    """获取新集物系统token"""
    url = 'https://erp.geehoop.com/api/backend/auth/login'
    headers = {'content-type': 'application/json'}
    data = {"mobile": "17313699441", "password": "17313699441"}
    response = requests.post(url, headers=headers, data=json.dumps(data))
    return response.json()['data']['token']


def get_alpha_token():
    """获取alpha系统token"""
    url = 'https://alpha.geehoop.com/api/backend/auth/login'
    headers = {'content-type': 'application/json'}
    data = {"mobile": "17313699441", "password": "17313699441"}
    response = requests.post(url, headers=headers, data=json.dumps(data))
    return response.json()['data']['token']


def unwind_array(arr):
    """展开数组"""
    res = []
    s = ''
    for i in range(len(arr)):
        s += str(arr[i]) + ','
    s = s[:-1]
    return s


def get_random_uuid(num):
    li = []
    for i in range(num):
        new_id = ObjectId()
        li.append(str(new_id))
    return li


def update_column_name(file, old_name, new_name):
    # 加载工作簿
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.active
    # 找到列名所在的列索引，并修改列名
    for col in sheet.iter_cols(1, sheet.max_column):
        if col[0].value == old_name:
            col[0].value = new_name
            break
    else:
        print(f"没有找到列名 {old_name}")
    # 保存工作簿
    workbook.save(file)
    print(f"列名 '{old_name}' 已成功修改为 '{new_name}'。")


def get_skuId(spuId, size):
    """根据spuId和尺码获取skuId"""
    result = jiwu.skus.find_one({"spuId": spuId, "properties": {"$regex": f'\"尺码\":\"{size}\"'}})
    if result is not None:
        return result['skuId']
    else:
        return None


def get_erp_token():
    """获取erp系统token"""
    url = 'https://apiv2.dingstock.net/trading/api/sale/backend/auth/login'
    headers = {'content-type': 'application/json'}
    data = {"mobile": "18161284908", "password": "18161284908"}
    response = requests.post(url, headers=headers, data=json.dumps(data))
    return response.json()['data']['token']


def get_taowu_token():
    url = 'https://taowu.geehoop.com/api/backend/auth/login'
    headers = {'content-type': 'application/json'}
    data = {"mobile": "18628929185t", "password": "edcasd000"}
    response = requests.post(url, headers=headers, data=json.dumps(data))
    return response.json()['data']['token']

# a = read_json_file(os.path.join(DOWNLOAD_PATH,'pao.json'))
# shoe = 0
# cloth = 0
# for i in a:
#     pattern = r'鞋(\d+)件'
#     pattern1 = r'服装(\d+)件'
#     num = re.findall(pattern, i['cou'])
#     num_c = re.findall(pattern1, i['cou'])
#     if num:
#         shoe += int(num[0])
#     if num_c:
#         cloth += int(num_c[0])
# print(shoe, cloth)
# a = read_json_file(os.path.join(DATA_PATH, '1.json'))['items']
# for i in a:
#     print(i['product']['release']['style_code'])


# f =  read_xlsx_file(os.path.join(DESKTOP_PATH, '服装.xlsx'), 0)
# li = []
# res = list(kream.products.find({"name":{"$in": f}}))
# for i in res:
#     if 'style_code' in i:
#         li.append([str(i['style_code'])])
# print(li)
# write_xlsx(['货号'], li, '服装')